package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Oms_cart_item13 {
	//插入
		@Test
		public void insert() {
			Connection conn=null;
			//准备数据库用户名，密码，url,driver
			String username="root";//用户名
			String password="root";//密码
			String driver="com.mysql.jdbc.Driver";//driver
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";//url
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//这是属于反射的内容
				//获取连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="insert into Oms_cart_item(id,product_id,product_sku_id,member_id,quantity,price,product_pic,product_name,product_sub_title,product_sku_code)values(1,2,33,44,23,08,22,88,60,40)";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"添加成功":"添加失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		//修改
		@Test
		public void  update() {
			Connection conn=null;
			//准备数据库用户名，密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			//协议://地址：端口号/数据库名？连接属性设置
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//属于反射的内容
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String  sql="update Oms_cart_item set quantity=6 where id=5";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row =st.executeUpdate(sql);
				System.out.println(row >0?"修改成功":"修改失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
				     try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn !=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
			
		}
		@Test
		public void delete() {
			Connection conn=null;
			//准备数据库用户名。密码，url，driver
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			//协议://地址：端口号/数据库名？连接属性设置
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中,这是属于反射的内容
				Class.forName(driver);
				//获取数据库连接java.sql.Connection
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
			String sql="delete from Oms_cart_item where id=2";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0? "删除成功":"删除失败");
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		//查询查一条
		@Test
		public void selet() {
			Connection conn = null;
			//1、准备数据库用户名、密码、url、driver
			String username = "root";
			String password = "root";
			String driver = "com.mysql.jdbc.Driver";
			//协议://地址:端口号/数据库名?连接属性设置
			String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);//这是属于反射的内容
				//获取数据库连接java.sql.Connection
				conn = DriverManager.getConnection(url, username, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备SQL命令
					String sql = "select * from Oms_cart_item";
					Statement st = null;
					ResultSet rs = null;
					try {
						//通过数据库连接conn获取事务java.sql.Statement对象st
						st = conn.createStatement();
						//执行sql命令
						rs = st.executeQuery(sql);
						//处理查询结果集
						while(rs.next()) {
							//rs.getInt(columnIndex)：根据索引获取列的值，强烈不推荐
							//获取id列的值  id,product_id,product_sku_id,member_id,quantity,price,product_pic,product_name,product_sub_title,product_sku_code
							//System.out.println(rs.getInt(1));
							//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
							System.out.println(rs.getInt("id"));
							System.out.println(rs.getObject("product_id"));
							System.out.println(rs.getObject("product_sku_id"));
							System.out.println(rs.getObject("member_id"));
							System.out.println(rs.getObject("quantity"));
							System.out.println(rs.getObject("price"));
							System.out.println(rs.getObject("product_pic"));
							System.out.println(rs.getObject("product_name"));
							System.out.println(rs.getObject("product_sub_title"));
							System.out.println(rs.getObject("product_sku_code"));
							//获取username列
							//System.out.println(rs.getString("username"));
						}
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						if(rs != null) {
							try {
								rs.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
						if(st != null) {
							try {
								st.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
						if(conn != null) {
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
		}
		}

}
